'''db_utils.py --- several usefull db-related routines for IOKS'''

import pyodbc


def get_cnxn():
    return pyodbc.connect('DSN=ioks;UID=user;PWD=password')

def drop_tables(cursor):
    tables = ('sites', 'pages', 'links', 'keywords',
              'page_to_link', 'page_to_keyword', 'link_to_keyword')
    for table_name in tables:
        try:
            cursor.execute('DELETE FROM %s;' % (table_name))
            cursor.execute('DROP TABLE %s;' % (table_name))
        except pyodbc.ProgrammingError:     # there's no such table yet
            pass
    cursor.execute("COMMIT");

def create_tables(cursor):
    cursor.execute('CREATE TABLE sites (site_id INT NOT NULL PRIMARY KEY,'
                                       'url TEXT NOT NULL,'
                                       'tci INT DEFAULT 0,'
                                       'external BOOL DEFAULT FALSE);')
    cursor.execute('CREATE UNIQUE INDEX sites_url ON sites(url(128));')
    cursor.execute('CREATE TABLE pages (page_id INT NOT NULL PRIMARY KEY,'
                                       'site_id INT NOT NULL REFERENCES sites,'
                                       'url TEXT NOT NULL,'
                                       'depth TINYINT NOT NULL,'
                                       'pr TINYINT DEFAULT 0,'
                                       'text TEXT NOT NULL,'
                                       'external BOOL DEFAULT FALSE);')
    cursor.execute('CREATE UNIQUE INDEX pages_url ON pages(url(256));')
    cursor.execute('CREATE INDEX pages_of_site ON pages(site_id);')
    cursor.execute('CREATE TABLE links (link_id INT NOT NULL PRIMARY KEY,'
                                       'page_id INT NOT NULL REFERENCES pages,'
                                       'title TEXT,'
                                       'text TEXT NOT NULL);')
    cursor.execute('CREATE TABLE keywords (keyword_id INT NOT NULL PRIMARY KEY,'
                                          'word VARCHAR(32) NOT NULL,'
                                          'popularity INT DEFAULT 0);')
    cursor.execute('CREATE UNIQUE INDEX word_index ON keywords(word);')
    cursor.execute('CREATE TABLE page_to_link (page_id INT NOT NULL REFERENCES pages,'
                                              'link_id INT NOT NULL REFERENCES links,'
                                              'num_links INT DEFAULT 1,'
                                              'PRIMARY KEY (page_id, link_id));')
    cursor.execute('CREATE TABLE page_to_keyword (page_id INT NOT NULL REFERENCES pages,'
                                                 'keyword_id INT NOT NULL REFERENCES keywords,'
                                                 'relevance FLOAT DEFAULT 0,'
                                                 'PRIMARY KEY (page_id, keyword_id));')
    cursor.execute('CREATE INDEX p2k ON page_to_keyword(keyword_id);')
    cursor.execute('CREATE TABLE link_to_keyword (link_id INT NOT NULL REFERENCES links,'
                                                 'keyword_id INT NOT NULL REFERENCES keywords,'
                                                 'relevance FLOAT DEFAULT 0,'
                                                 'PRIMARY KEY (link_id, keyword_id));')
    cursor.execute("COMMIT");

def add_to_db(cursor, smth):
    names = [k for k in smth.__dict__ if k != 'table_name']
    values = [repr(smth.__dict__[name]) for name in names]
    query = 'INSERT INTO %s(%s) VALUES(%s);'
    query = query % (smth.__dict__['table_name'],
                     ', '.join(names), ', '.join(values))
    return cursor.execute(query)

